<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="mybatis.mappers.UserMapper">
    <resultMap id="user" type="models.entities.UserEntity">
        <result property="email" column="email"/>
        <result property="password" column="password"/>
        <result property="name" column="userName"/>
        <result property="address" column="address"/>
        <result property="phoneNumber" column="phoneNumber"/>
        <result property="id" column="userId"/>
        <result property="sessionId" column="sessionId"/>
        <result property="registerDate" column="registerDate"/>
    </resultMap>
    <delete id="deleteUser">
        delete from users where userId = #{userId};
        delete from user_profiles where userId = #{userId};
    </delete>
    <insert id="register" parameterType="models.entities.UserEntity">
        insert into user_profiles (email, password, userId) values(#{email}, #{password}, #{id});
    </insert>
    <select id="getUser" resultMap="user">
        select * from user_profiles where email = #{email} limit 1;
    </select>
    <select id="getById" resultMap="user">
        select * from user_profiles where userId = #{id};
    </select>
    <select id="authenticate" resultMap="user">
        select * from user_profiles where email = #{email} and password = #{password} limit 1;
    </select>
    <insert id="addAnonymousUser" parameterType="models.entities.UserEntity">
        insert into users(sessionId)
        select #{sessionId}
        from dual
        where not exists
        (select * from users where sessionId = #{sessionId});
        <selectKey keyProperty="id" resultType="int" order="AFTER">
            select userId from users where sessionId = #{sessionId};
        </selectKey>
    </insert>
    <update id="mergeShoppingItems">
        update shopping_items set userId = #{authenticatedUserId} where userId = #{anonymousUserId};
    </update>
    <update id="update">
        update user_profiles set
            userName = #{name},
            phoneNumber = #{phoneNumber},
            address = #{address}
            where userId = #{id};
    </update>
    <update id="changePassword">
        update user_profiles
        set password = #{newPassword}
        where email = #{username} and password = #{password};
    </update>
    <select id="getUserListTotalCount" resultType="int">
        select count(u.userId) from users u
        left join user_profiles up on up.userId = u.userId;
    </select>
    <select id="getUserList" resultMap="user">
         select
            u.userId,
            u.registerDate,
            up.email,
            up.userName,
            up.phoneNumber,
            up.address
        from users u
        left join user_profiles up on up.userId = u.userId
        limit #{offset}, #{pageSize};
    </select>
    <update id="setIsAdmin">
        delete from users_roles where userId = #{id} and roleId = (select roleId from roles where title = 'admin' limit 1);
        <if test="isAdmin">
            insert into users_roles(userId, roleId) values(#{id}, (select roleId from roles where title = 'admin' limit 1));
            </if>
    </update>
    <select id="getUserRoles" resultType="java.lang.String">
        select r.title from users_roles ur
        left join roles r on r.roleId = ur.roleId
        where ur.userId = #{id};
    </select>
    <select id="getAdminEmails" resultType="java.lang.String">
        select up.email from users_roles ur
        left join user_profiles up on up.userId = ur.userId
        where ur.roleId = (select roleId from roles where title = 'admin');
    </select>
</mapper>